[CHAPTER 03. Shared Buffer와 Wait Event]

4. CASE STUDY
4-1. CASE1: Data Scan 범위


DROP TABLE IF EXISTS buf_test;
CREATE TABLE buf_test AS
SELECT  no AS a,
        md5(random()::TEXT) AS b,
        floor(no / 5000) AS c
FROM    pg_catalog.generate_series(1, 10000) AS no;

/* Sequential Scan */
EXPLAIN 
SELECT   a,
         b,
         c
FROM     buf_test
WHERE    a = 5000;   

pg_lw_lock_tracer -p 509470 -v --statistics >> buf_test_1.log

SELECT   a,
         b,
         c
FROM     buf_test
WHERE    a = 5000; 

/* Index Scan */
CREATE INDEX buf_test_idx ON buf_test (a);

EXPLAIN 
SELECT   a,
         b,
         c
FROM     buf_test
WHERE    a = 5000;  

pg_lw_lock_tracer -p 509470 -v --statistics >> buf_test_2.log

SELECT   a,
         b,
         c
FROM     buf_test
WHERE    a = 5000; 


4-2. CASE2: Shared Buffer 크기


DROP TABLE IF EXISTS bufsize_test;
CREATE TABLE bufsize_test AS
SELECT  no AS a,
        md5(random()::TEXT) AS b,
        floor(no / 5000) AS c
FROM    pg_catalog.generate_series(1, 10000000) AS no;

SELECT   a,
         b,
         c
FROM     bufsize_test
WHERE    a = $1;

SELECT   pwsp.pid,
         pwsp.event_type,
         pwsp.event,
         pwsp.count,
         pss.query AS query_text
FROM     pg_wait_sampling_profile pwsp
JOIN     pg_stat_statements pss
         ON pwsp.queryid = pss.queryid
WHERE    pwsp.event_type = 'LWLock'
  AND    pwsp.event = 'BufferMapping'
ORDER BY pwsp.pid;


4-3. CASE3: 불필요한 인덱스


DROP TABLE IF EXISTS dup_test;
CREATE TABLE dup_test AS
SELECT    no AS a,
          md5(random()::TEXT) AS b,
          floor(no / 5000) AS c
FROM      pg_catalog.generate_series(1, 10) AS no;

CREATE INDEX dup_test_idx1 ON dup_test (a);
CREATE INDEX dup_test_idx2 ON dup_test (a, b);
CREATE INDEX dup_test_idx3 ON dup_test (a, b, c);

/* 인덱스 제거 전 */
SELECT   tablename,
         indexname,
         indexdef
FROM     pg_catalog.pg_indexes
WHERE    tablename = 'dup_test';

pg_lw_lock_tracer -p 509470 -v --statistics >> dup_test_1.log

UPDATE   dup_test
SET      a = a * 2
WHERE    mod(a, 2) = 0;

/* 인덱스 제거 후 */
DROP INDEX IF EXISTS dup_test_idx1;
DROP INDEX IF EXISTS dup_test_idx2;

SELECT   tablename,
         indexname,
         indexdef
FROM     pg_catalog.pg_indexes
WHERE    tablename = 'dup_test';

pg_lw_lock_tracer -p 509470 -v --statistics >> dup_test_2.log

UPDATE   dup_test
SET      a = a * 2
WHERE    mod(a, 2) = 0;


4-4. CASE4: 외래키 제약 조건


DROP TABLE IF EXISTS parent_test, child_test CASCADE;

CREATE TABLE parent_test (
    a SMALLINT PRIMARY KEY,
    b CHAR(10)
);
CREATE TABLE child_test (
    a SMALLINT,
    b CHAR(10),
    FOREIGN KEY (a) REFERENCES parent_test (a)
);

INSERT INTO parent_test
SELECT   generate_series(1, 100),
         lpad('1', 10, 'x');

INSERT INTO child_test
SELECT   generate_series(1, 100),
         lpad('y', 10);
         

/* 외래키 제약 조건 설정 */         
SELECT   tc.constraint_name AS constraint_name,
         tc.table_name      AS child_table,
         kcu.column_name    AS child_column,
         ccu.table_name     AS foreign_table,
         ccu.column_name    AS foreign_column
FROM     information_schema.table_constraints tc
JOIN     information_schema.key_column_usage kcu
         ON tc.constraint_name::TEXT = kcu.constraint_name::TEXT
JOIN     information_schema.constraint_column_usage ccu
         ON ccu.constraint_name::TEXT = tc.constraint_name::TEXT
WHERE    tc.constraint_type = 'FOREIGN KEY'
  AND    tc.table_name = 'child_test';      
  
pg_lw_lock_tracer -p 509470 -v --statistics >> fk_test_1.log

UPDATE   child_test
SET      a = 1
WHERE    mod(a, 9) = 0;     

/* 외래키 제약 조건 삭제 */
ALTER TABLE child_test
DROP CONSTRAINT IF EXISTS child_test_a_fkey;

SELECT   tc.constraint_name AS constraint_name,
         tc.table_name      AS child_table,
         kcu.column_name    AS child_column,
         ccu.table_name     AS foreign_table,
         ccu.column_name    AS foreign_column
FROM     information_schema.table_constraints tc
JOIN     information_schema.key_column_usage kcu
         ON tc.constraint_name::TEXT = kcu.constraint_name::TEXT
JOIN     information_schema.constraint_column_usage ccu
         ON ccu.constraint_name::TEXT = tc.constraint_name::TEXT
WHERE    tc.constraint_type = 'FOREIGN KEY'
  AND    tc.table_name = 'child_test';
  
pg_lw_lock_tracer -p 509470 -v --statistics >> fk_test_2.log

UPDATE   child_test
SET      a = 1
WHERE    mod(a, 9) = 0;  

